package com.ep.instruktor.plac;

import java.util.Calendar;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;

import com.ep.general.GeneralClassMdt;
import com.ep.instruktor.plac.db.PlacManewrowy;

public class PlacManewrowyMdt extends GeneralClassMdt<PlacManewrowy> {

	public PlacManewrowyMdt() {
		super(PlacManewrowy.class);
	}

	public List<PlacManewrowy> getPlacInstruktor(int instruktorId) {
		getSession().beginTransaction();
		Criteria crit = getSession().createCriteria(PlacManewrowy.class);
		crit.add(Restrictions.eq("instruktor.id", instruktorId));
		crit.addOrder(Order.asc("data"));
		crit.addOrder(Order.asc("godzinaPoczatkowa"));

		return list(crit);
	}

	public List<PlacManewrowy> getPlac() {
		getSession().beginTransaction();
		Criteria crit = getSession().createCriteria(PlacManewrowy.class);
		crit.addOrder(Order.asc("data"));
		crit.addOrder(Order.asc("godzinaPoczatkowa"));

		return list(crit);
	}

	public List<PlacManewrowy> getPlacByMonth() {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT * FROM i_d_plac_manewrowy_rezerwacja WHERE MONTH(data) = ");
		sb.append(Calendar.getInstance().get(Calendar.MONTH) + 1);
		sb.append(" ORDER BY data, godzina_poczatkowa ASC");

		getSession().beginTransaction();
		SQLQuery query = getSession().createSQLQuery(sb.toString());
		query.addEntity(PlacManewrowy.class);

		return list(query);
	}

	public List<PlacManewrowy> getPlacByMonthInstruktor(int instruktorId) {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT * FROM i_d_plac_manewrowy_rezerwacja WHERE MONTH(data) = ");
		sb.append(Calendar.getInstance().get(Calendar.MONTH) + 1);
		sb.append(" AND d_instruktor_id=");
		sb.append(instruktorId);
		sb.append(" ORDER BY data, godzina_poczatkowa ASC");

		getSession().beginTransaction();
		SQLQuery query = getSession().createSQLQuery(sb.toString());
		query.addEntity(PlacManewrowy.class);

		return list(query);
	}

	public List<PlacManewrowy> getPlacByWeek() {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT * FROM i_d_plac_manewrowy_rezerwacja");
		sb.append(" WHERE data >= CURDATE()");
		sb.append(" AND data <= DATE_ADD(CURDATE(), INTERVAL 7 DAY)");
		sb.append(" ORDER BY data, godzina_poczatkowa ASC");

		getSession().beginTransaction();
		SQLQuery query = getSession().createSQLQuery(sb.toString());
		query.addEntity(PlacManewrowy.class);

		return list(query);
	}

	public List<PlacManewrowy> getPlacByWeekInstruktor(int instruktorId) {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT * FROM i_d_plac_manewrowy_rezerwacja");
		sb.append(" WHERE data >= CURDATE()");
		sb.append(" AND data <= DATE_ADD(CURDATE(), INTERVAL 7 DAY) AND d_instruktor_id=");
		sb.append(instruktorId);
		sb.append(" ORDER BY data, godzina_poczatkowa ASC");

		getSession().beginTransaction();
		SQLQuery query = getSession().createSQLQuery(sb.toString());
		query.addEntity(PlacManewrowy.class);

		return list(query);
	}

	public boolean deleteOldRezerwations() {
		StringBuilder sb = new StringBuilder();
		sb.append("delete from i_d_plac_manewrowy_rezerwacja ");
		sb.append("Where id not in ( ");
		sb.append("Select h.d_plac_manewrowy_id from i_d_harmonogram_kursu h where h.d_plac_manewrowy_id is not null ");
		sb.append(") and data < CURDATE()");
		

		getSession().beginTransaction();
		Query query = getSession().createSQLQuery(sb.toString());
		query.executeUpdate();

		return true;
	}

}
